"""
This program in used to create test and train data
"""


"""
@ Uses thes are the scripts are created to merge xml to mysql posts  (xml to mysql  ==> with xml to mysql so)
"""
import os
import MySQLdb
import sys
import xml.etree.cElementTree as ElementTree
reload(sys)
sys.setdefaultencoding('utf-8')


table_type = "posts"
file_path = "D:\JIIT\PRML\project\dataset\Posts"
database = "prml"
user = "root"
password="123"
port=3306
table = "posts"

db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
insert_cursor = db1.cursor() 


file = ["bicycle.stackexchange"]
for line in file:
    site = line.strip() +".com"
    print  "site=",site
       
    source = file_path+".xml"
    # get an iterable
    context = ElementTree.iterparse(source, events=("start", "end"))

    # turn it into an iterator
    context = iter(context)

    # get the root element
    event, root = context.next()
    count1 = 0  
    for event, child_of_root in context:
        if event == "end" and child_of_root.tag == "row":
            id= (str)(child_of_root.attrib['Id'])
            #print "id=",id
            count = 0
            Body = ""
            Title = ""
            try:
                Body=(str)(child_of_root.attrib['Body'])
                Body = Body.replace("\n"," ")
                Body = Body.replace("'"," ")
                Body = Body.replace("\""," ")
                Body = Body.replace(",", " ")
                Body = Body.replace("(", " ")
                Body = Body.replace(")", " ")
                Body = Body.replace("\\", " ")
              
            except Exception ,e:
                #print "error"            
                count = count+1
                #print e, "count1=", count
             
            try:
                Title=(str)(child_of_root.attrib['Title'])
                Title = Title.replace("\n"," ")
                Title = Title.replace("'"," ")
                Title = Title.replace("\""," ")
                Title = Title.replace(",", " ")
                Title = Title.replace("(", " ")
                Title = Title.replace(")", " ")
                Title = Title.replace("\\", " ")
               
            except Exception ,e:
                #print "error"            
                count = count+1
                #print e, "count1=", count
        
            try:
                OwnerUserId=(str)(child_of_root.attrib['OwnerUserId'])
            except Exception, e:
                OwnerUserId="-1"
                count  =count+1 
                
            try:
                OwnerDisplayName=(str)(child_of_root.attrib['OwnerDisplayName'])
                OwnerDisplayName  = OwnerDisplayName.replace("'"," ")
            except Exception, e:
                OwnerDisplayName="-1"
                count  =count+1                   
        
            try:
               Tags=(str)(child_of_root.attrib['Tags'])
            except Exception, e:
                Tags=''
                count  =count+1           
     
           
            insert_str = "insert into `"+ table+ "` values("+child_of_root.attrib['Id']+","+child_of_root.attrib['PostTypeId']+\
            ",'"+ Body+"',"+(str)(OwnerUserId)+",'"+ Title+"','"+Tags+"')"
              
            print "insert=", insert_str
            insert_cursor.execute(insert_str)
        root.clear()
    
    db1.commit()  
        
        
        
         